#include "teachersql.h"
#include<QDebug>
#include<QBitArray>
TeacherSql::TeacherSql()
{
    db = QSqlDatabase::addDatabase("QMYSQL");//添加mysql
}

void TeacherSql::ConnectSql(const QString &hostname)
{

        db.setHostName(hostname);
        db.setUserName("root");
        db.setPassword("123456");
        db.setDatabaseName("test268");
        db.setPort(3306);
        if(!db.open())
        {
            qDebug() << db.lastError() <<endl;
            return ;
        }
        qDebug() << "打开成功\n";
}

void TeacherSql::SetTeacherId(const QString &TeacherId)
{
    this->TeacherId=TeacherId;
}

void TeacherSql::SetTeacherInfo()
{
     QSqlQuery query;
     query.exec("drop table `教师表`;");
     query.exec("drop table  `学生表`");

     query.exec("create table `教师表`(教师工号 int ,姓名 text,头像 longblob,性别 text,出生日期 text,户口所在地 text,手机号 text,邮箱 text);");
     query.exec("create table  `学生表`(学号 int,姓名 text,头像 longblob,性别 text,出生日期 text,现居城市 text,户口所在地 text,手机号 text,电子邮箱 text,缺勤次数 int,班级 text,学习阶段 text,就业去向 text,授课老师);");

     query.exec("insert into `教师表`(教师工号 ,姓名 ,性别 ,出生日期 ,户口所在地 ,手机号 ,邮箱 ) values(1,'zhangsan','男','9.21','合肥','1234',123456789@qq.com);");
     query.exec("insert into 学生表(学号,姓名,性别,出生日期,现居城市,户口所在地,手机号,电子邮箱,缺勤次数,班级,学习阶段,就业去向) values(1,'zhangsan','男','9.1','南京','合肥','1234','1234@','3','268','一','合肥');");
     query.exec("insert into 学生表(学号,姓名,性别,出生日期,现居城市,户口所在地,手机号,电子邮箱,缺勤次数,班级,学习阶段,就业去向) values(2,'zhang','男','9.11','南京','合肥','12345','1234@5','1','268','一','合肥');");
     query.exec("insert into 学生表(学号,姓名,性别,出生日期,现居城市,户口所在地,手机号,电子邮箱,缺勤次数,班级,学习阶段,就业去向) values(3,'san','男','9.12','南京','合肥','12346','1234@4','2','268','一','合肥');");
}

void TeacherSql::SetStuScoreInfo()
{
    QSqlQuery query;
    query.exec("drop table StuScore;");
    if(!query.exec("create table StuScore(学号 int primary key,第一阶段1 int,第一阶段2 int,第一阶段3 int,第一阶段4 int,第二阶段1 int,第二阶段2 int,第二阶段3 int,第三阶段1 int,第三阶段2 int,第三阶段3 int,第一阶段评语 text,第二阶段评语 text,第三阶段评语 text);"))
    {


          qDebug()<<query.lastError()<<endl;

   }

}


void TeacherSql::GetInfoFromSql(QStringList &l)
{
    QSqlQuery query(db);

    QString Key = "1";//主键
    QString table = "teacher";//表名
    QString Getname = "select `姓名` from " + table + " where `教师工号` = '" + Key+"';";
    QString Getsex = "select `性别` from " + table + " where `教师工号` = '" + Key+"';";
    QString Getbirthday = "select `出生日期`from " + table + " where `教师工号` = '" + Key+"';";
    QString Gethome = "select `户口所在地` from " + table + " where `教师工号` = '" + Key+"';";
    QString Getphone = "select `手机号` from " + table + " where `教师工号` = '" + Key+"';";
    QString Getemail = "select `邮箱`from " + table + " where `教师工号` = '" + Key+"';";

    QStringList list;
    list.push_back(std::move(Getname));
    list.push_back(std::move(Getsex));
    list.push_back(std::move(Getbirthday));
    list.push_back(std::move(Gethome));
    list.push_back(std::move(Getphone));
    list.push_back(std::move(Getemail));

    int i = 0;
    for(const auto&ite:list){
        if(query.exec(ite)&&query.next()){
            l.append(query.value(0).toString());
        }else {
            {
                qDebug()<<"Query execution falied:"<<query.lastError().text();
            }
        }
    }
}

void TeacherSql::SavePhotoToSql(const QPixmap&pixmap)
{

    // 将图片转换为二进制数据
    QByteArray imageData;
    QBuffer buffer(&imageData);
    buffer.open(QIODevice::WriteOnly);
    pixmap.save(&buffer, "PNG"); // 保存为PNG格式
    buffer.close();

    // 将二进制数据保存到数据库中
    QSqlQuery query(db);
    QString savephoto="update `教师表` set `头像` = :imageData where `教师工号` ='"+ TeacherId+"';";
    query.prepare(savephoto);
    query.bindValue(":imageData",imageData);
    query.exec();

}

QPixmap TeacherSql::GetPhotoFromSql()
{
    QSqlQuery query(db);
    if(query.exec("select`头像`from `教师表` where `教师工号`='"+TeacherId+"';"))
    {
        if(query.next())
        {
            QByteArray imageInfo=query.value("头像").toByteArray();//取出图片数据
            QPixmap m;
            m.loadFromData(imageInfo);
            return m;

        }
    }

}



